package org.qpcr.community.project.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hswebframework.ezorm.core.param.QueryParam;
import org.hswebframework.ezorm.core.param.Sort;
import org.hswebframework.ezorm.core.param.Term;
import org.hswebframework.reactor.excel.CellDataType;
import org.hswebframework.reactor.excel.ExcelHeader;
import org.hswebframework.reactor.excel.spec.SheetSpec;
import org.hswebframework.web.api.crud.entity.PagerResult;
import org.hswebframework.web.api.crud.entity.QueryParamEntity;
import org.hswebframework.web.crud.service.GenericReactiveCrudService;
import org.qpcr.community.device.entity.DeviceProperty;
import org.qpcr.community.device.service.data.DeviceDataService;
import org.qpcr.community.elastic.search.service.ElasticSearchService;
import org.qpcr.community.io.file.FileProperties;
import org.qpcr.community.project.model.entity.CTMetadada;
import org.qpcr.community.project.model.entity.ExperimentalRecordsInstanceEntity;
import org.qpcr.community.project.model.entity.ExportAmplificationParam;
import org.qpcr.community.project.model.entity.excel.*;
import org.qpcr.community.project.web.excel.sequence.Group;
import org.qpcr.community.project.web.excel.sequence.Panel;
import org.qpcr.community.project.web.excel.sequence.SequenceStep;
import org.springframework.core.io.buffer.DataBufferUtils;
import org.springframework.http.codec.multipart.FilePart;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestPart;
import reactor.core.publisher.*;

import java.io.File;
import java.util.*;
import java.util.function.Consumer;

import org.springframework.core.io.buffer.DataBuffer;
import org.springframework.core.io.buffer.DefaultDataBufferFactory;
import org.springframework.http.MediaType;
import org.springframework.http.server.reactive.ServerHttpResponse;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.io.*;
import java.nio.file.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Service
@Slf4j
@RequiredArgsConstructor
public class LocalExperimentalRecordsInstanceService extends GenericReactiveCrudService<ExperimentalRecordsInstanceEntity, String> {
    private final DeviceDataService deviceDataService;
    private final ElasticSearchService elasticSearchService;
    private final FileProperties properties;
    // 缓存温度sheet的数据行，用于与电压电流按行号合并
    private final ThreadLocal<Map<String, Map<String, Object>>> temperatureMergedBuffer =
        ThreadLocal.withInitial(HashMap::new);
    // 缓存熔解（后处理）sheet的数据行，用于与熔解结果按行号合并
    private final ThreadLocal<Map<String, Map<String, Object>>> meltProcessedMergedBuffer =
        ThreadLocal.withInitial(HashMap::new);

    /**
     * 实验信息
     *
     * @param recordId
     * @param experimentalRecordsInstanceEntity
     * @return
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getRecordSheet(String recordId, ExperimentalRecordsInstanceEntity experimentalRecordsInstanceEntity, String type) {

        if (type.equals("en")) {//英文版
            Flux<Map<String, Object>> dataStream =
                this.findById(recordId).flatMapMany(entity -> {
                    SequenceStep sequenceStep = JSON.parseObject(entity.getSequence(), SequenceStep.class);
                    List<Group> groups = sequenceStep.getGroups();
                    return Flux.create(fluxSink -> {
                        Map<String, String> groupList = new HashMap<>();
                        groupList.put("1", "Constant");
                        groupList.put("2", "Cycle");
                        groupList.put("3", "Melting");
                        for (int i = 0; i < groups.size(); i++) {
                            Map<String, Object> map = new HashMap<>();
                            Group group = groups.get(i);
                            String loop = group.getLoopCount();
                            if (loop == null || loop.equals("1")) {
                                loop = "";
                            } else {
                                loop = "(" + loop + ")";
                            }
                            boolean testlight = group.isTestLight();

                            List<Panel> panels = group.getPanels();
                            for (int j = 0; j < panels.size(); j++) {
                                Panel panel = panels.get(j);
                                map.put("i", i + 1);
                                map.put("type", groupList.get(group.getType()) + loop);
                                map.put("temperature", panel.getTemperature() + "℃");
                                map.put("speed", Double.parseDouble(panel.getSpeed()));
                                map.put("delay", Double.parseDouble(panel.getDelay()));
                                String light = testlight ? "Y" : "";
                                light = j == panels.size() - 1 ? light : "";
                                map.put("light", light);
                                map.put("data", entity);
                                fluxSink.next(map);
                            }
                        }
                        fluxSink.complete();
                    });
                });
            return sheet -> {
//
//            设备名称（ID）_模块名称（ID）_实验人员账号
//（载体编码）
//            检测项：（库+检测项名）或者（用户自定义+检测项名），测光通道：（库 + 测光通道）或者（用户自定义 + 测光通道），程序：（库 + 程序名称）或者（用户自定义 + 程序名称）
//            完成度：x%，实验时长：x分x秒

                String title = experimentalRecordsInstanceEntity.getDeviceName() + "(" + experimentalRecordsInstanceEntity.getDeviceId() + ")_" + experimentalRecordsInstanceEntity.getModuleCode() + "(" + experimentalRecordsInstanceEntity.getModuleId() + ")_"
                    + experimentalRecordsInstanceEntity.getCreatorName() + "(" + experimentalRecordsInstanceEntity.getCreatorUserName() + ")"
                    + "\n" + experimentalRecordsInstanceEntity.getCarrier();
                String itemStr = "\nDetection:（";
                if (experimentalRecordsInstanceEntity.getItemUpStatus() == null || experimentalRecordsInstanceEntity.getItemUpStatus() == 0) {
                    itemStr += "Database";
                } else {
                    itemStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                itemStr += experimentalRecordsInstanceEntity.getItem() + "),";
                String lightStr = "Channel:（";
                if (experimentalRecordsInstanceEntity.getLightUpStatus() == null || experimentalRecordsInstanceEntity.getLightUpStatus() == 0) {
                    lightStr += "Database";
                } else {
                    lightStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                lightStr += experimentalRecordsInstanceEntity.getLightChannel() + "),";
                String seqStr = "Program:（";
                if (experimentalRecordsInstanceEntity.getSeqUpStatus() == null || experimentalRecordsInstanceEntity.getSeqUpStatus() == 0) {
                    seqStr += "Database";
                } else {
                    seqStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                seqStr += experimentalRecordsInstanceEntity.getSeqName() + "),";


                int millis = Integer.parseInt(experimentalRecordsInstanceEntity.getRunTime());
                int seconds = (millis / 1000) % 60; // 计算秒数
                int minutes = millis / 60000; // 计算分钟

                title += itemStr + lightStr + seqStr + "\nProgress:" + experimentalRecordsInstanceEntity.getState() + "%" + "Time：" + minutes + "分" + seconds + "秒";

                sheet.name("Info")
                     .cell(0, 0, title)
                     //自定义sheet操作
                     .option(sheet_ -> {
                         sheet_.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));
                     });
                sheet.header(new ExcelHeader("i", "No.", CellDataType.NUMBER))
                     .header("type", "Type")
                     .header(new ExcelHeader("temperature", "Temp", CellDataType.NUMBER))
                     .header(new ExcelHeader("speed", "Rate（Celsius Degree/s）", CellDataType.NUMBER))
                     .header(new ExcelHeader("delay", "Time", CellDataType.NUMBER))
                     .header("light", "Fluorometric Detection Enabled")
                     .rows(dataStream)
                     .firstRowIndex(1);
            };
        } else {
            Flux<Map<String, Object>> dataStream =
                this.findById(recordId).flatMapMany(entity -> {
                    SequenceStep sequenceStep = JSON.parseObject(entity.getSequence(), SequenceStep.class);
                    List<Group> groups = sequenceStep.getGroups();
                    return Flux.create(fluxSink -> {
                        Map<String, String> groupList = new HashMap<>();
                        groupList.put("1", "恒温段");
                        groupList.put("2", "变温段");
                        groupList.put("3", "熔解段");
                        for (int i = 0; i < groups.size(); i++) {
                            Map<String, Object> map = new HashMap<>();
                            Group group = groups.get(i);
                            String loop = group.getLoopCount();
                            if (loop == null || loop.equals("1")) {
                                loop = "";
                            } else {
                                loop = "(" + loop + ")";
                            }
                            boolean testlight = group.isTestLight();

                            List<Panel> panels = group.getPanels();
                            for (int j = 0; j < panels.size(); j++) {
                                Panel panel = panels.get(j);
                                map.put("i", i + 1);
                                map.put("type", groupList.get(group.getType()) + loop);
                                map.put("temperature", panel.getTemperature() + "℃");
                                map.put("speed", Double.parseDouble(panel.getSpeed()));
                                map.put("delay", Double.parseDouble(panel.getDelay()));
                                String light = testlight ? "是" : "";
                                light = j == panels.size() - 1 ? light : "";
                                map.put("light", light);
                                map.put("data", entity);
                                fluxSink.next(map);
                            }
                        }
                        fluxSink.complete();
                    });
                });
            return sheet -> {
//
//            设备名称（ID）_模块名称（ID）_实验人员账号
//（载体编码）
//            检测项：（库+检测项名）或者（用户自定义+检测项名），测光通道：（库 + 测光通道）或者（用户自定义 + 测光通道），程序：（库 + 程序名称）或者（用户自定义 + 程序名称）
//            完成度：x%，实验时长：x分x秒

                String title = experimentalRecordsInstanceEntity.getDeviceName() + "(" + experimentalRecordsInstanceEntity.getDeviceId() + ")_" + experimentalRecordsInstanceEntity.getModuleCode() + "(" + experimentalRecordsInstanceEntity.getModuleId() + ")_"
                    + experimentalRecordsInstanceEntity.getCreatorName() + "(" + experimentalRecordsInstanceEntity.getCreatorUserName() + ")"
                    + "\n" + experimentalRecordsInstanceEntity.getCarrier();
                String itemStr = "\n检测项:（";
                if (experimentalRecordsInstanceEntity.getItemUpStatus() == null || experimentalRecordsInstanceEntity.getItemUpStatus() == 0) {
                    itemStr += "库";
                } else {
                    itemStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                itemStr += experimentalRecordsInstanceEntity.getItem() + "),";
                String lightStr = "测光通道:（";
                if (experimentalRecordsInstanceEntity.getLightUpStatus() == null || experimentalRecordsInstanceEntity.getLightUpStatus() == 0) {
                    lightStr += "库";
                } else {
                    lightStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                lightStr += experimentalRecordsInstanceEntity.getLightChannel() + "),";
                String seqStr = "程序:（";
                if (experimentalRecordsInstanceEntity.getSeqUpStatus() == null || experimentalRecordsInstanceEntity.getSeqUpStatus() == 0) {
                    seqStr += "库";
                } else {
                    seqStr += experimentalRecordsInstanceEntity.getName();
                }
                ;
                seqStr += experimentalRecordsInstanceEntity.getSeqName() + "),";


                int millis = Integer.parseInt(experimentalRecordsInstanceEntity.getRunTime());
                int seconds = (millis / 1000) % 60; // 计算秒数
                int minutes = millis / 60000; // 计算分钟

                title += itemStr + lightStr + seqStr + "\n完成度:" + experimentalRecordsInstanceEntity.getState() + "%" + "实验时长：" + minutes + "分" + seconds + "秒";

                sheet.name("实验信息")
                     .cell(0, 0, title)
                     //自定义sheet操作
                     .option(sheet_ -> {
                         sheet_.addMergedRegion(CellRangeAddress.valueOf("A1:F1"));
                     });
                sheet.header(new ExcelHeader("i", "序号", CellDataType.NUMBER))
                     .header("type", "类型")
                     .header(new ExcelHeader("temperature", "温度", CellDataType.NUMBER))
                     .header(new ExcelHeader("speed", "速度（Celsius Degree/s）", CellDataType.NUMBER))
                     .header(new ExcelHeader("delay", "时间", CellDataType.NUMBER))
                     .header("light", "是否测光")
                     .rows(dataStream)
                     .firstRowIndex(1);
            };
        }


    }

    /**
     * 设备状态
     *
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getDeviceStateData(ExperimentalRecordsInstanceEntity experimentalRecordsInstanceEntity, String type) {

//        String jsonString = "{\"batchId\":\"\",\"calibration\":0,\"carrier\":1,\"ch1_init_status\":1,\"ch1_run_status\":0,\"ch2_init_status\":1,\"ch2_run_status\":0,\"ch3_init_status\":1,\"ch3_run_status\":0,\"ch4_init_status\":1,\"ch4_run_status\":0,\"cold_head_temp\":0,\"cover_plate\":1,\"cover_plate_carrier_status\":0,\"heater_err_str\":\"\",\"heater_init_stauts\":1,\"heater_reference_temp\":3000,\"heater_rtd_temp\":3000,\"heater_status\":1,\"init\":1,\"leave_time\":10000,\"schedule\":100,\"status\":1,\"water_cooler_box_temp\":0,\"water_cooler_err_str\":\"\",\"water_cooler_errer\":0,\"water_cooler_init_status\":1,\"water_cooler_seft_check\":3}";
        ObjectMapper mapper = new ObjectMapper();
        Map<String, String> finalMap = null;
        try {
            finalMap = mapper.readValue(experimentalRecordsInstanceEntity.getModuleState(), new TypeReference<Map<String, String>>() {
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String, String> finalMap1 = finalMap;


        if (type.equals("en")) {//英文版
            return sheet -> sheet
                .name("Status")
                .cell(0, 0, "Software Version")
                .cell(0, 1, experimentalRecordsInstanceEntity.getSoftwareVersion())
                .cell(1, 0, "Module Version")
                .cell(1, 1, experimentalRecordsInstanceEntity.getModuleVersion())
                .cell(2, 0, "Module Init. Verification")
                .cell(2, 1, "0".equals(finalMap1.get("init")) ? "Fail" : "Pass")
                .cell(3, 0, "Heater Init. Verification")
                .cell(3, 1, "0".equals(finalMap1.get("heater_init_stauts")) ? "Fail" : "Pass")
                .cell(4, 0, "Heater Calibration Verification")
                .cell(4, 1, "0".equals(finalMap1.get("calibration")) ? "Fail" : "Pass")
                .cell(5, 0, "Heater Parameters R1")
                .cell(5, 1, "R1(" + finalMap1.get("R1") + "),R2(" + finalMap1.get("R2") + "),H2(" + finalMap1
                    .get("H2") + ")")
                .cell(6, 0, "Heater Status")
                .cell(6, 1, "".equals(finalMap1.get("heater_err_str")) ? "Normal" : "Error:" + finalMap1.get("heater_err_str"))
                .cell(7, 0, "Cooler Init. Verification")
                .cell(7, 1, "0".equals(finalMap1.get("water_cooler_init_status")) ? "Fail" : "Pass")
                .cell(8, 0, "Cooler Status")
                .cell(8, 1, "0".equals(finalMap1.get("water_cooler_errer")) ? "Normal" : "Error:" + finalMap1.get("water_cooler_err_str"))
                .cell(9, 0, "Channel1 Init. Verification")
                .cell(9, 1, "0".equals(finalMap1.get("ch1_init_status")) ? "Fail" : "Pass")
                .cell(10, 0, "Channel1 Status")
                .cell(10, 1, "0".equals(finalMap1.get("ch1_run_status")) ? "Normal" : "Error:" + finalMap1.get("ch1_err_str"))
                .cell(11, 0, "Channel2 Init. Verification")
                .cell(11, 1, "0".equals(finalMap1.get("ch2_init_status")) ? "Fail" : "Pass")
                .cell(12, 0, "Channel2 Status")
                .cell(12, 1, "0".equals(finalMap1.get("ch2_run_status")) ? "Normal" : "Error:" + finalMap1.get("ch2_err_str"))
                .cell(13, 0, "Channel3 Init. Verification")
                .cell(13, 1, "0".equals(finalMap1.get("ch3_init_status")) ? "Fail" : "Pass")
                .cell(14, 0, "Channel3 Status")
                .cell(14, 1, "0".equals(finalMap1.get("ch3_run_status")) ? "Normal" : "Error：" + finalMap1.get("ch3_err_str"))
                .cell(15, 0, "Channel4 Init. Verification")
                .cell(15, 1, "0".equals(finalMap1.get("ch4_init_status")) ? "Fail" : "Pass")
                .cell(16, 0, "Channel4 Status")
                .cell(16, 1, "0".equals(finalMap1.get("ch4_run_status")) ? "Normal" : "Error:" + finalMap1.get("ch4_err_str"));

        } else {
            return sheet -> sheet
                .name("状态信息")
                .cell(0, 0, "设备端上位机版本号")
                .cell(0, 1, experimentalRecordsInstanceEntity.getSoftwareVersion())
                .cell(1, 0, "设备端下位机版本号")
                .cell(1, 1, experimentalRecordsInstanceEntity.getModuleVersion())
                .cell(2, 0, "模块初始化状态")
                .cell(2, 1, "0".equals(finalMap1.get("init")) ? "否" : "是")
                .cell(3, 0, "加热器初始化状态")
                .cell(3, 1, "0".equals(finalMap1.get("heater_init_stauts")) ? "否" : "是")
                .cell(4, 0, "加热器校准状态")
                .cell(4, 1, "0".equals(finalMap1.get("calibration")) ? "否" : "是")
                .cell(5, 0, "加热器参数")
                .cell(5, 1, "R1(" + finalMap1.get("R1") + "),R2(" + finalMap1.get("R2") + "),H2(" + finalMap1
                    .get("H2") + ")")
                .cell(6, 0, "加热器健康状态")
                .cell(6, 1, "".equals(finalMap1.get("heater_err_str")) ? "正常" : "异常:" + finalMap1.get("heater_err_str"))
                .cell(7, 0, "冷却器初始化状态")
                .cell(7, 1, "0".equals(finalMap1.get("water_cooler_init_status")) ? "否" : "是")
                .cell(8, 0, "冷却器健康状态")
                .cell(8, 1, "0".equals(finalMap1.get("water_cooler_errer")) ? "正常" : "异常：" + finalMap1.get("water_cooler_err_str"))
                .cell(9, 0, "测光通道1初始化状态")
                .cell(9, 1, "0".equals(finalMap1.get("ch1_init_status")) ? "否" : "是")
                .cell(10, 0, "测光通道1健康状态")
                .cell(10, 1, "0".equals(finalMap1.get("ch1_run_status")) ? "正常" : "异常:" + finalMap1.get("ch1_err_str"))
                .cell(11, 0, "测光通道2初始化状态")
                .cell(11, 1, "0".equals(finalMap1.get("ch2_init_status")) ? "否" : "是")
                .cell(12, 0, "测光通道2健康状态")
                .cell(12, 1, "0".equals(finalMap1.get("ch2_run_status")) ? "正常" : "异常:" + finalMap1.get("ch2_err_str"))
                .cell(13, 0, "测光通道3初始化状态")
                .cell(13, 1, "0".equals(finalMap1.get("ch3_init_status")) ? "否" : "是")
                .cell(14, 0, "测光通道3健康状态")
                .cell(14, 1, "0".equals(finalMap1.get("ch3_run_status")) ? "正常" : "异常：" + finalMap1.get("ch3_err_str"))
                .cell(15, 0, "测光通道4初始化状态")
                .cell(15, 1, "0".equals(finalMap1.get("ch4_init_status")) ? "否" : "是")
                .cell(16, 0, "测光通道4健康状态")
                .cell(16, 1, "0".equals(finalMap1.get("ch4_run_status")) ? "正常" : "异常:" + finalMap1.get("ch4_err_str"));

        }


    }

    /**
     * 获取温度
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getTemperatureReportData(String recordId, String deviceId, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "TemperatureReportData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           Double sample_time = jsonObject.get("sample_time") == null ? 0.00 : jsonObject.getDoubleValue("sample_time") / 1000;

                                                           map.put("sample_time", String.format("%.2f", sample_time));
                                                           map.put("temp_rtd_expect", jsonObject.get("temp_rtd_expect") == null ? "" : jsonObject.getDoubleValue("temp_rtd_expect"));
                                                           map.put("reserved_power", jsonObject.get("reserved_power") == null ? "" : jsonObject.getDoubleValue("reserved_power"));
                                                           map.put("temp_goal", jsonObject.get("temp_goal") == null ? "" : jsonObject.getDoubleValue("temp_goal"));
                                                           map.put("temp_forecast", jsonObject.get("temp_forecast") == null ? "" : jsonObject.getDoubleValue("temp_forecast"));
                                                           map.put("temp_rtd_actual", jsonObject.get("temp_rtd_actual") == null ? "" : jsonObject.getDoubleValue("temp_rtd_actual"));
                                                           map.put("temp_cold_head", jsonObject.get("temp_cold_head") == null ? "" : jsonObject.getDoubleValue("temp_cold_head"));
                                                           map.put("Assisted_RTD_A", jsonObject.get("Assisted_RTD_A") == null ? "" : jsonObject.getDoubleValue("Assisted_RTD_A"));
                                                           map.put("Assisted_RTD_B", jsonObject.get("Assisted_RTD_B") == null ? "" : jsonObject.getDoubleValue("Assisted_RTD_B"));
                                                           map.put("Assisted_RTD_C", jsonObject.get("Assisted_RTD_C") == null ? "" : jsonObject.getDoubleValue("Assisted_RTD_C"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));

        return sheet -> sheet.name(type.equals("en") ? "Temp" : "温度")
                             .header(new ExcelHeader("sample_time", "Time (s)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temp_rtd_expect", "RTD_Des (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("reserved_power", "Reagent_Tar(Celsius Degree)", CellDataType.NUMBER))//Reserve_Power (Celsius Degree)
                             .header(new ExcelHeader("temp_goal", "Set (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temp_forecast", "Reagent (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temp_rtd_actual", "RTD_Real (Celsius Degree)", CellDataType.NUMBER)) //RTD_Real (Celsius Degree)
                             .header(new ExcelHeader("temp_cold_head", "Cooler (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("Assisted_RTD_A", "Assisted_RTD_A(Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("Assisted_RTD_B", "Assisted_RTD_B(Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("Assisted_RTD_C", "Assisted_RTD_C(Celsius Degree)", CellDataType.NUMBER))
                             .rows(dataStream);

    }

    /**
     * 获取电流电压
     * uint16_t rtd_vol; //RTD两端电压值 y轴 Voltage
     * uint16_t rtd_cur; //RTD的电流值 y轴 Current
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getVolAndCurrent(String recordId, String deviceId, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "TemperatureReportData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           Double sample_time = jsonObject.get("sample_time") == null ? 0.00 : jsonObject.getDoubleValue("sample_time") / 1000;

                                                           map.put("sample_time", String.format("%.2f", sample_time));
//                        map.put("sample_time", jsonObject.get("sample_time") == null ? "" : jsonObject.getDoubleValue("sample_time") / 1000);
                                                           map.put("rtd_vol", jsonObject.get("rtd_vol") == null ? "" : jsonObject.getDoubleValue("rtd_vol"));
                                                           map.put("rtd_cur", jsonObject.get("rtd_cur") == null ? "" : jsonObject.getDoubleValue("rtd_cur"));
                                                           map.put("AVoltage", jsonObject.get("AVoltage") == null ? "" : jsonObject.getDoubleValue("AVoltage"));
                                                           map.put("ACurrent", jsonObject.get("ACurrent") == null ? "" : jsonObject.getDoubleValue("ACurrent"));
                                                           map.put("BVoltage", jsonObject.get("BVoltage") == null ? "" : jsonObject.getDoubleValue("BVoltage"));
                                                           map.put("BCurrent", jsonObject.get("BCurrent") == null ? "" : jsonObject.getDoubleValue("BCurrent"));
                                                           map.put("CVoltage", jsonObject.get("CVoltage") == null ? "" : jsonObject.getDoubleValue("CVoltage"));
                                                           map.put("CCurrent", jsonObject.get("CCurrent") == null ? "" : jsonObject.getDoubleValue("CCurrent"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));
        return sheet -> sheet.name(type.equals("en") ? "Current&Voltage" : "电压电流")
                             .header(new ExcelHeader("sample_time", "Time (s)", CellDataType.NUMBER))
                             .header(new ExcelHeader("rtd_vol", "Main_Voltage (V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("rtd_cur", "Main_Current (A)", CellDataType.NUMBER))
                             .header(new ExcelHeader("AVoltage", "A_Voltage (V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ACurrent", "A_Current (A)", CellDataType.NUMBER))
                             .header(new ExcelHeader("BVoltage", "B_Voltage (V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("BCurrent", "B_Current (A)", CellDataType.NUMBER))
                             .header(new ExcelHeader("CVoltage", "C_Voltage (V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("CCurrent", "C_Current (A)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 获取荧光
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeteringDataReport(String recordId, String deviceId, boolean calc, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeteringDataReport", calc)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           map.put("count", jsonObject.get("count") == null ? "" : jsonObject.getIntValue("count"));
                                                           map.put("ch1_value", jsonObject.get("ch1_value") == null ? "" : jsonObject.getInteger("ch1_value"));
                                                           map.put("ch2_value", jsonObject.get("ch2_value") == null ? "" : jsonObject.getInteger("ch2_value"));
                                                           map.put("ch3_value", jsonObject.get("ch3_value") == null ? "" : jsonObject.getInteger("ch3_value"));
                                                           map.put("ch4_value", jsonObject.get("ch4_value") == null ? "" : jsonObject.getInteger("ch4_value"));
                                                           map.put("ch1_original", jsonObject.get("ch1_original") == null ? "" : jsonObject.getDoubleValue("ch1_original"));
                                                           map.put("ch2_original", jsonObject.get("ch2_original") == null ? "" : jsonObject.getDoubleValue("ch2_original"));
                                                           map.put("ch3_original", jsonObject.get("ch3_original") == null ? "" : jsonObject.getDoubleValue("ch3_original"));
                                                           map.put("ch4_original", jsonObject.get("ch4_original") == null ? "" : jsonObject.getDoubleValue("ch4_original"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));
        return sheet -> sheet.name(type.equals("en") ? "Fluore(Volt)" : "荧光（电压）")
                             .header(new ExcelHeader("count", "Cycle", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_value", "Fluore(VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_value", "Fluore(Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_value", "Fluore(FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_value", "Fluore(ROX)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_original", "Fluore(VIC) Original(0.1V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_original", "Fluore(Cy5) Original(0.1V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_original", "Fluore(FAM) Original(0.1V)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_original", "Fluore(ROX) Original(0.1V)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 获取荧光(去串扰)
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeteringSaveDataReport(String recordId, String deviceId, boolean calc, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeteringSaveData", calc)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       // 使用Set来存储已处理的count值，实现去重
                                                       Set<Object> processedCounts = new HashSet<>();

                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           Object countValue = jsonObject.get("count") == null ? "" : jsonObject.getIntValue("count");

                                                           // 检查count是否已经处理过，如果已处理则跳过
                                                           if (processedCounts.contains(countValue)) {
                                                               continue;
                                                           }

                                                           // 将count添加到已处理集合中
                                                           processedCounts.add(countValue);

                                                           map.put("count", countValue);
                                                           map.put("vic", jsonObject.get("vic") == null ? "" : jsonObject.getDoubleValue("vic"));
                                                           map.put("cy5", jsonObject.get("cy5") == null ? "" : jsonObject.getDoubleValue("cy5"));
                                                           map.put("fam", jsonObject.get("fam") == null ? "" : jsonObject.getDoubleValue("fam"));
                                                           map.put("rox", jsonObject.get("rox") == null ? "" : jsonObject.getDoubleValue("rox"));
                                                           map.put("ch_vic", jsonObject.get("ch_vic") == null ? "" : jsonObject.getDoubleValue("ch_vic"));
                                                           map.put("ch_cy5", jsonObject.get("ch_cy5") == null ? "" : jsonObject.getDoubleValue("ch_cy5"));
                                                           map.put("ch_fam", jsonObject.get("ch_fam") == null ? "" : jsonObject.getDoubleValue("ch_fam"));
                                                           map.put("ch_rox", jsonObject.get("ch_rox") == null ? "" : jsonObject.getDoubleValue("ch_rox"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));

        return sheet -> sheet.name(type.equals("en") ? "Fluore(Raw)" : "荧光(源数据)")
                             .header(new ExcelHeader("count", "Cycle", CellDataType.NUMBER))
                             .header(new ExcelHeader("vic", "Fluore(VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("cy5", "Fluore(Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("fam", "Fluore(FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("rox", "Fluore(ROX)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch_vic", "Fluore(VIC) Original", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch_cy5", "Fluore(Cy5) Original", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch_fam", "Fluore(FAM) Original", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch_rox", "Fluore(ROX) Original", CellDataType.NUMBER))
                             .rows(dataStream);
    }


    /**
     * 获取扩增
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getAmplificationData(String recordId, String deviceId, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "AmplificationData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           map.put("cycle", jsonObject.get("cycle") == null ? "" : jsonObject.getIntValue("cycle"));
                                                           map.put("ch1_light", jsonObject.get("ch1_light") == null ? "" : jsonObject.getDoubleValue("ch1_light"));
                                                           map.put("ch2_light", jsonObject.get("ch2_light") == null ? "" : jsonObject.getDoubleValue("ch2_light"));
                                                           map.put("ch3_light", jsonObject.get("ch3_light") == null ? "" : jsonObject.getDoubleValue("ch3_light"));
                                                           map.put("ch4_light", jsonObject.get("ch4_light") == null ? "" : jsonObject.getDoubleValue("ch4_light"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));
        return sheet -> sheet.name(type.equals("en") ? "Amplification" : "扩增")
                             .header(new ExcelHeader("cycle", "Cycle", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_light", "Fluore (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light", "Fluore (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light", "Fluore (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light", "Fluore (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 获取计算库计算之后的扩增数据
     *
     * @param recordId                 批次id
     * @param exportAmplificationParam
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getAmplificationData(String recordId, ExportAmplificationParam exportAmplificationParam, String type) {


        if (exportAmplificationParam.hasAmplificationData()) {

            Flux<Map<String, Object>> dataStream = Flux.create(fluxSink -> {
                JSONArray meteringData = exportAmplificationParam.getMeteringSaveDataAxis();

                for (int i = 0; i < meteringData.size(); i++) {
                    Map<String, Object> map = new HashMap<>();
                    map.put("cycle", meteringData.getIntValue(i));
                    map.put("ch1_light", 0);
                    map.put("ch2_light", 0);
                    map.put("ch3_light", 0);
                    map.put("ch4_light", 0);
                    fluxSink.next(map);
                }

                fluxSink.complete();
            });

            return sheet -> sheet.name(type.equals("en") ? "Amplification" : "扩增")
                                 .header(new ExcelHeader("cycle", "Cycle", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch1_light", "Fluore (VIC)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch2_light", "Fluore (Cy5)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch3_light", "Fluore (FAM)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch4_light", "Fluore (ROX)", CellDataType.NUMBER))
                                 .rows(dataStream);
        } else {
            return this.getAmplificationData(recordId, exportAmplificationParam.getDeviceId(), type);
        }


    }

    /**
     * 获取CT
     *
     * @param recordId 批次id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getCT(String recordId) {
        Flux<Map<String, Object>> dataStream = this.findById(recordId).flatMapMany(entity -> Flux.create(fluxSink -> {
            Map<String, Object> map = new HashMap<>();
            map.put("vic", entity.getVic());
            map.put("cy5", entity.getCy5());
            map.put("fam", entity.getFam());
            map.put("rox", entity.getRox());
            fluxSink.next(map);
            fluxSink.complete();
        }));
        return sheet -> sheet.name("CT")
                             .header(new ExcelHeader("vic", "Fluore (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("cy5", "Fluore (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("fam", "Fluore (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("rox", "Fluore (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 替换获取计算后的CT
     *
     * @param recordId 批次id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getCT(String recordId, ExportAmplificationParam exportAmplificationParam) {
        if (exportAmplificationParam.hasAmplificationData()) {
            Flux<Map<String, Object>> dataStream = Flux.create(fluxSink -> {
                JSONArray meteringData = exportAmplificationParam.getMeteringSaveData();
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < meteringData.size(); j++) {
                    JSONObject channel = meteringData.getJSONObject(j);
                    String name = channel.getString("name");
                    switch (name) {
                        case "VIC":
                            map.put("vic", meteringData.getJSONObject(j).getDoubleValue("ct"));
                            break;
                        case "Cy5":
                            map.put("cy5", meteringData.getJSONObject(j).getDoubleValue("ct"));
                            break;
                        case "FAM":
                            map.put("fam", meteringData.getJSONObject(j).getDoubleValue("ct"));
                            break;
                        case "ROX":
                            map.put("rox", meteringData.getJSONObject(j).getDoubleValue("ct"));
                            break;
                    }

                }
                fluxSink.next(map);
                fluxSink.complete();
            });

            return sheet -> sheet.name("CT")
                                 .header(new ExcelHeader("vic", "Fluore (VIC)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("cy5", "Fluore (Cy5)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("fam", "Fluore (FAM)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("rox", "Fluore (ROX)", CellDataType.NUMBER))
                                 .rows(dataStream);
        } else {
            return getCT(recordId);
        }
    }

    /**
     * 计算库的CT
     *
     * @param recordId 批次id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getCalCT(String recordId) {
        Flux<Map<String, Object>> dataStream = this.findById(recordId).flatMapMany(entity -> Flux.create(fluxSink -> {
            Map<String, Object> map = new HashMap<>();
            map.put("vic", entity.getVic());
            map.put("cy5", entity.getCy5());
            map.put("fam", entity.getFam());
            map.put("rox", entity.getRox());
            fluxSink.next(map);
            fluxSink.complete();
        }));
        return sheet -> sheet.name("CT")
                             .header(new ExcelHeader("vic", "Fluore (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("cy5", "Fluore (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("fam", "Fluore (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("rox", "Fluore (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }


    /**
     * 计算库标准曲线分析结果
     *
     * @param recordId
     * @param experimentalRecordsInstanceEntity
     * @return
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getCurveSheet(String recordId, ExperimentalRecordsInstanceEntity experimentalRecordsInstanceEntity) {
        Flux<Map<String, Object>> dataStream =
            this.findById(recordId).flatMapMany(entity -> {
                SequenceStep sequenceStep = JSON.parseObject(entity.getSequence(), SequenceStep.class);
                List<Group> groups = sequenceStep.getGroups();
                return Flux.create(fluxSink -> {
                    Map<String, String> groupList = new HashMap<>();
                    groupList.put("1", "恒温段");
                    groupList.put("2", "变温段");
                    groupList.put("3", "熔解段");
                    for (int i = 0; i < groups.size(); i++) {
                        Map<String, Object> map = new HashMap<>();
                        Group group = groups.get(i);
                        String loop = group.getLoopCount();
                        if (loop == null || loop.equals("1")) {
                            loop = "";
                        } else {
                            loop = "(" + loop + ")";
                        }
                        boolean testlight = group.isTestLight();

                        List<Panel> panels = group.getPanels();
                        for (int j = 0; j < panels.size(); j++) {
                            Panel panel = panels.get(j);
                            map.put("i", i + 1);
                            map.put("type", groupList.get(group.getType()) + loop);
                            map.put("temperature", panel.getTemperature() + "℃");
                            map.put("speed", Double.parseDouble(panel.getSpeed()));
                            map.put("delay", Double.parseDouble(panel.getDelay()));
                            String light = testlight ? "是" : "";
                            light = j == panels.size() - 1 ? light : "";
                            map.put("light", light);
                            map.put("data", entity);
                            fluxSink.next(map);
                        }
                    }
                    fluxSink.complete();
                });
            });
        return sheet -> {
//
//            设备名称（ID）_模块名称（ID）_实验人员账号
//（载体编码）
//            检测项：（库+检测项名）或者（用户自定义+检测项名），测光通道：（库 + 测光通道）或者（用户自定义 + 测光通道），程序：（库 + 程序名称）或者（用户自定义 + 程序名称）
//            完成度：x%，实验时长：x分x秒

            String title = experimentalRecordsInstanceEntity.getDeviceName() + "(" + experimentalRecordsInstanceEntity.getDeviceId() + ")_" + experimentalRecordsInstanceEntity.getModuleCode() + "(" + experimentalRecordsInstanceEntity.getModuleId() + ")_"
                + experimentalRecordsInstanceEntity.getCreatorName() + "(" + experimentalRecordsInstanceEntity.getCreatorUserName() + ")"
                + "\n" + experimentalRecordsInstanceEntity.getCarrier();
            String itemStr = "\n检测项:（";
            if (experimentalRecordsInstanceEntity.getItemUpStatus() == null || experimentalRecordsInstanceEntity.getItemUpStatus() == 0) {
                itemStr += "库";
            } else {
                itemStr += "用户自定义";
            }
            ;
            itemStr += experimentalRecordsInstanceEntity.getItem() + "),";
            String lightStr = "测光通道:（";
            if (experimentalRecordsInstanceEntity.getLightUpStatus() == null || experimentalRecordsInstanceEntity.getLightUpStatus() == 0) {
                lightStr += "库";
            } else {
                lightStr += "用户自定义";
            }
            ;
            lightStr += experimentalRecordsInstanceEntity.getLightChannel() + "),";
            String seqStr = "程序:（";
            if (experimentalRecordsInstanceEntity.getSeqUpStatus() == null || experimentalRecordsInstanceEntity.getSeqUpStatus() == 0) {
                seqStr += "库";
            } else {
                seqStr += "用户自定义";
            }
            ;
            seqStr += experimentalRecordsInstanceEntity.getSeqName() + "),";


            int millis = Integer.parseInt(experimentalRecordsInstanceEntity.getRunTime());
            int seconds = (millis / 1000) % 60; // 计算秒数
            int minutes = millis / 60000; // 计算分钟

            title += itemStr + lightStr + seqStr + "\n完成度:" + experimentalRecordsInstanceEntity.getState() + "%" + "实验时长：" + minutes + "分" + seconds + "秒";

            sheet.name("实验信息")
                 .cell(0, 0, title)
                 //自定义sheet操作
                 .option(sheet_ -> {
                     sheet_.addMergedRegion(CellRangeAddress.valueOf("A1:E1"));
                 });
            sheet.header(new ExcelHeader("i", "序号", CellDataType.NUMBER))
                 .header("type", "类型")
                 .header(new ExcelHeader("temperature", "温度", CellDataType.NUMBER))
                 .header(new ExcelHeader("speed", "速度（Celsius Degree/s）", CellDataType.NUMBER))
                 .header(new ExcelHeader("delay", "时间", CellDataType.NUMBER))
                 .header("light", "是否测光")
                 .rows(dataStream)
                 .firstRowIndex(1);
        };
    }

    /**
     * 获取熔解（源数据）
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltCurveData(String recordId, String deviceId, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeltCurveData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                       for (int i = 0; i < result.getData().size(); i++) {
                                                           Map<String, Object> map = new HashMap<>();
                                                           DeviceProperty deviceProperty = result.getData().get(i);
                                                           JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                           map.put("temperatur1", jsonObject.get("temperatur1") == null ? "" : jsonObject.getString("temperatur1"));
                                                           map.put("temperatur2", jsonObject.get("temperatur2") == null ? "" : jsonObject.getString("temperatur2"));
                                                           map.put("temperatur3", jsonObject.get("temperatur3") == null ? "" : jsonObject.getString("temperatur3"));
                                                           map.put("temperatur4", jsonObject.get("temperatur4") == null ? "" : jsonObject.getString("temperatur4"));
                                                           map.put("ch1_light", jsonObject.get("ch1_light") == null ? "" : jsonObject.getString("ch1_light"));
                                                           map.put("ch2_light", jsonObject.get("ch2_light") == null ? "" : jsonObject.getString("ch2_light"));
                                                           map.put("ch3_light", jsonObject.get("ch3_light") == null ? "" : jsonObject.getString("ch3_light"));
                                                           map.put("ch4_light", jsonObject.get("ch4_light") == null ? "" : jsonObject.getString("ch4_light"));
                                                           fluxSink.next(map);
                                                       }
                                                       fluxSink.complete();
                                                   }));
        return sheet -> sheet.name(type.equals("en") ? "Melting(Raw)" : "熔解（原数据）")
                             .header(new ExcelHeader("temperatur1", "VIC_T (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_light", "Fluore (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur2", "Cy5_T (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light", "Fluore (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur3", "FAM_T (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light", "Fluore (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur4", "ROX_T (Celsius Degree)", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light", "Fluore (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 替换-获取熔解（源数据）
     *
     * @param recordId                 批次id
     * @param exportAmplificationParam 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltCurveData(String recordId, ExportAmplificationParam exportAmplificationParam, String type) {

        if (exportAmplificationParam.hasMeltCurveData()) {

            Flux<Map<String, Object>> dataStream = Flux.create(fluxSink -> {
                JSONArray meltCurveData = exportAmplificationParam.getMeltCurveData();
                if (meltCurveData == null || meltCurveData.isEmpty()) {
                    fluxSink.complete();
                    return;
                }

                // 获取第一个通道的data数组长度作为基准
                JSONObject firstChannel = meltCurveData.getJSONObject(0);
                JSONArray dataArray = firstChannel.getJSONArray("data");
                int size = dataArray.size();

                for (int i = 0; i < size; i++) {
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < meltCurveData.size(); j++) {
                        if (j >= meltCurveData.size()) break;

                        JSONObject channel = meltCurveData.getJSONObject(j);
                        if (!channel.containsKey("data")) continue;

                        JSONArray channelData = channel.getJSONArray("data");
                        if (i >= channelData.size()) continue;

                        String name = channel.getString("name");
                        double dataValue = channelData.getDouble(i);

                        switch (name) {
                            case "VIC":
                                map.put("ch1_light", dataValue);
                                map.put("temperatur1", 0.0);
                                break;
                            case "Cy5":
                                map.put("ch2_light", dataValue);
                                map.put("temperatur2", 0.0);
                                break;
                            case "FAM":
                                map.put("ch3_light", dataValue);
                                map.put("temperatur3", 0.0);
                                break;
                            case "ROX":
                                map.put("ch4_light", dataValue);
                                map.put("temperatur4", 0.0);
                                break;
                        }
                    }
                    fluxSink.next(map);
                }
                fluxSink.complete();
            });
            return sheet -> sheet.name(type.equals("en") ? "Melting(Raw)" : "熔解（原数据）")
                                 .header(new ExcelHeader("temperatur1", "VIC_T (Celsius Degree)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch1_light", "Fluore (VIC)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur2", "Cy5_T (Celsius Degree)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch2_light", "Fluore (Cy5)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur3", "FAM_T (Celsius Degree)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch3_light", "Fluore (FAM)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur4", "ROX_T (Celsius Degree)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch4_light", "Fluore (ROX)", CellDataType.NUMBER))
                                 .rows(dataStream);
        } else {
            return this.getMeltCurveData(recordId, exportAmplificationParam.getDeviceId(), type);
        }


    }

    /**
     * 替换-获取熔解（计算后）
     *
     * @param recordId                 批次id
     * @param exportAmplificationParam 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltCurveCuleData(String recordId, ExportAmplificationParam exportAmplificationParam, String type) {
        if (exportAmplificationParam.hasMeltCurveData()) {

            Flux<Map<String, Object>> dataStream = Flux.create(fluxSink -> {
                JSONArray meltCurveData = exportAmplificationParam.getMeltCurveData();
                if (meltCurveData == null || meltCurveData.isEmpty()) {
                    fluxSink.complete();
                    return;
                }

                // 获取第一个通道的data数组长度作为基准
                JSONObject firstChannel = meltCurveData.getJSONObject(0);
                JSONArray dataArray = firstChannel.getJSONArray("data");
                int size = dataArray.size();

                for (int i = 0; i < size; i++) {
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < meltCurveData.size(); j++) {
                        if (j >= meltCurveData.size()) break;

                        JSONObject channel = meltCurveData.getJSONObject(j);
                        if (!channel.containsKey("data")) continue;

                        JSONArray channelData = channel.getJSONArray("data");
                        if (i >= channelData.size()) continue;

                        String name = channel.getString("name");
                        double dataValue = channelData.getDouble(i);

                        switch (name) {
                            case "VIC":
                                map.put("ch1_light_d", dataValue);
                                map.put("temperatur1", 0.0);
                                break;
                            case "Cy5":
                                map.put("ch2_light_d", dataValue);
                                map.put("temperatur2", 0.0);
                                break;
                            case "FAM":
                                map.put("ch3_light_d", dataValue);
                                map.put("temperatur3", 0.0);
                                break;
                            case "ROX":
                                map.put("ch4_light_d", dataValue);
                                map.put("temperatur4", 0.0);
                                break;
                        }
                    }
                    fluxSink.next(map);
                }
                fluxSink.complete();
            });
            return sheet -> sheet.name(type.equals("en") ? "Melting(Derivative)" : "熔解（后处理）")
                                 .header(new ExcelHeader("temperatur1", "T_VIC", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch1_light_d", "Diff (VIC)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur2", "T_Cy5", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch2_light_d", "Diff (Cy5)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur3", "T_FAM", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch3_light_d", "Diff (FAM)", CellDataType.NUMBER))
                                 .header(new ExcelHeader("temperatur4", "T_ROX", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch4_light_d", "Diff (ROX)", CellDataType.NUMBER))
                                 .rows(dataStream);
        } else {
            return this.getMeltCurveCuleData(recordId, exportAmplificationParam.getDeviceId(), type);
        }
    }

    /**
     * 获取熔解（计算后）
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltCurveCuleData(String recordId, String deviceId, String type) {
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeltCurveCalcData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                                    for (int i = 0; i < result.getData().size(); i++) {
                                                                        Map<String, Object> map = new HashMap<>();
                                                                        DeviceProperty deviceProperty = result.getData().get(i);
                                                                        JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                                        map.put("temperatur1", jsonObject.get("temperatur1") == null ? "" : jsonObject.getString("temperatur1"));
                                                                        map.put("ch1_light_d", jsonObject.get("ch1_light_d") == null ? "" : jsonObject.getString("ch1_light_d"));
                                                                        map.put("temperatur2", jsonObject.get("temperatur2") == null ? "" : jsonObject.getString("temperatur2"));
                                                                        map.put("ch2_light_d", jsonObject.get("ch2_light_d") == null ? "" : jsonObject.getString("ch2_light_d"));
                                                                        map.put("temperatur3", jsonObject.get("temperatur3") == null ? "" : jsonObject.getString("temperatur3"));
                                                                        map.put("ch3_light_d", jsonObject.get("ch3_light_d") == null ? "" : jsonObject.getString("ch3_light_d"));
                                                                        map.put("temperatur4", jsonObject.get("temperatur4") == null ? "" : jsonObject.getString("temperatur4"));
                                                                        map.put("ch4_light_d", jsonObject.get("ch4_light_d") == null ? "" : jsonObject.getString("ch4_light_d"));
                                                                        fluxSink.next(map);
                                                                    }
                                                                    fluxSink.complete();
                                                                })
                                                   );
        return sheet -> sheet.name(type.equals("en") ? "Melting(Derivative)" : "熔解（后处理）")
                             .header(new ExcelHeader("temperatur1", "T_VIC", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_light_d", "Diff (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur2", "T_Cy5", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light_d", "Diff (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur3", "T_FAM", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light_d", "Diff (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur4", "T_ROX", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light_d", "Diff (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 获取计算库计算的熔解（计算后）
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltCurveCuleData1(String recordId, String deviceId) {


        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeltCurveCalcData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                                    for (int i = 0; i < result.getData().size(); i++) {
                                                                        Map<String, Object> map = new HashMap<>();
                                                                        DeviceProperty deviceProperty = result.getData().get(i);
                                                                        JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                                        map.put("temperatur1", jsonObject.get("temperatur1") == null ? "" : jsonObject.getString("temperatur1"));
                                                                        map.put("ch1_light_d", jsonObject.get("ch1_light_d") == null ? "" : jsonObject.getString("ch1_light_d"));
                                                                        map.put("temperatur2", jsonObject.get("temperatur2") == null ? "" : jsonObject.getString("temperatur2"));
                                                                        map.put("ch2_light_d", jsonObject.get("ch2_light_d") == null ? "" : jsonObject.getString("ch2_light_d"));
                                                                        map.put("temperatur3", jsonObject.get("temperatur3") == null ? "" : jsonObject.getString("temperatur3"));
                                                                        map.put("ch3_light_d", jsonObject.get("ch3_light_d") == null ? "" : jsonObject.getString("ch3_light_d"));
                                                                        map.put("temperatur4", jsonObject.get("temperatur4") == null ? "" : jsonObject.getString("temperatur4"));
                                                                        map.put("ch4_light_d", jsonObject.get("ch4_light_d") == null ? "" : jsonObject.getString("ch4_light_d"));
                                                                        fluxSink.next(map);
                                                                    }
                                                                    fluxSink.complete();
                                                                })
                                                   );
        return sheet -> sheet.name("熔解（计算后）")
                             .header(new ExcelHeader("temperatur1", "T_VIC", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_light_d", "Diff (VIC)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur2", "T_Cy5", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light_d", "Diff (Cy5)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur3", "T_FAM", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light_d", "Diff (FAM)", CellDataType.NUMBER))
                             .header(new ExcelHeader("temperatur4", "T_ROX", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light_d", "Diff (ROX)", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 获取Trim
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getTrim(String recordId, String deviceId, String type) {
        // todo 未知数据源
        Flux<Map<String, Object>> dataStream = this.getData(recordId, deviceId, "MeltCurveCalcData", false)
                                                   .flatMapMany(result -> Flux.create(fluxSink -> {
                                                                    for (int i = 0; i < result.getData().size(); i++) {
                                                                        Map<String, Object> map = new HashMap<>();
                                                                        DeviceProperty deviceProperty = result.getData().get(i);
                                                                        JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                                                        map.put("ch1_light_t", jsonObject.get("ch1_light_t") == null ? "" : updateValue(jsonObject.getString("ch1_light_t")));
                                                                        map.put("ch1_light_rm", jsonObject.get("ch1_light_rm") == null ? "" : updateValue(jsonObject.getString("ch1_light_rm")));
                                                                        map.put("ch2_light_t", jsonObject.get("ch2_light_t") == null ? "" : updateValue(jsonObject.getString("ch2_light_t")));
                                                                        map.put("ch2_light_rm", jsonObject.get("ch2_light_rm") == null ? "" : updateValue(jsonObject.getString("ch2_light_rm")));
                                                                        map.put("ch3_light_t", jsonObject.get("ch3_light_t") == null ? "" : updateValue(jsonObject.getString("ch3_light_t")));
                                                                        map.put("ch3_light_rm", jsonObject.get("ch3_light_rm") == null ? "" : updateValue(jsonObject.getString("ch3_light_rm")));
                                                                        map.put("ch4_light_t", jsonObject.get("ch4_light_t") == null ? "" : updateValue(jsonObject.getString("ch4_light_t")));
                                                                        map.put("ch4_light_rm", jsonObject.get("ch4_light_rm") == null ? "" : updateValue(jsonObject.getString("ch4_light_rm")));
                                                                        fluxSink.next(map);
                                                                    }
                                                                    fluxSink.complete();
                                                                })
                                                   );

        return sheet -> sheet.name(type.equals("en") ? "Melt Curve Result" : "熔解结果")
                             .header(new ExcelHeader("ch1_light_t", "Tm_VIC", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch1_light_rm", "Rm_VIC", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light_t", "Tm_Cy5", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch2_light_rm", "Rm_Cy5", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light_t", "Tm_FAM", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch3_light_rm", "Rm_FAM", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light_t", "Tm_ROX", CellDataType.NUMBER))
                             .header(new ExcelHeader("ch4_light_rm", "Rm_ROX", CellDataType.NUMBER))
                             .rows(dataStream);
    }

    /**
     * 替换-计算的熔解结果，获取rm tm
     *
     * @param recordId                 批次id
     * @param exportAmplificationParam 设备id
     * @return Consumer<SheetSpec.HeaderSheetSpec>
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getTrim(String recordId, ExportAmplificationParam exportAmplificationParam, String type) {

        if (exportAmplificationParam.hasMeltCurveData()) {


            Flux<Map<String, Object>> dataStream = Flux.create(fluxSink -> {
                JSONArray meltCurveData = exportAmplificationParam.getMeltCurveData();
                if (meltCurveData == null || meltCurveData.isEmpty()) {
                    fluxSink.complete();
                    return;
                }

                // 获取第一个通道的tm数组长度作为基准
                JSONObject firstChannel = meltCurveData.getJSONObject(0);
                JSONArray tmArray = firstChannel.getJSONArray("tm");
                JSONArray rmArray = firstChannel.getJSONArray("rm");
                int size = Math.min(tmArray.size(), rmArray.size());

                for (int i = 0; i < size; i++) {
                    Map<String, Object> map = new HashMap<>();
                    for (int j = 0; j < meltCurveData.size(); j++) {
                        if (j >= meltCurveData.size()) break; // 防止索引越界

                        JSONObject channel = meltCurveData.getJSONObject(j);
                        if (!channel.containsKey("tm") || !channel.containsKey("rm")) continue;

                        JSONArray tm = channel.getJSONArray("tm");
                        JSONArray rm = channel.getJSONArray("rm");
                        if (i >= tm.size() || i >= rm.size()) continue; // 跳过不完整的条目

                        String name = channel.getString("name");
                        double tmValue = tm.getDouble(i);
                        double rmValue = rm.getDouble(i);

                        switch (name) {
                            case "VIC":
                                map.put("ch1_light_t", tmValue);
                                map.put("ch1_light_rm", rmValue);
                                break;
                            case "Cy5":
                                map.put("ch2_light_t", tmValue);
                                map.put("ch2_light_rm", rmValue);
                                break;
                            case "FAM":
                                map.put("ch3_light_t", tmValue);
                                map.put("ch3_light_rm", rmValue);
                                break;
                            case "ROX":
                                map.put("ch4_light_t", tmValue);
                                map.put("ch4_light_rm", rmValue);
                                break;
                        }
                    }
                    fluxSink.next(map);
                }
                fluxSink.complete();
            });

            return sheet -> sheet.name(type.equals("en") ? "Melt Curve Result" : "熔解结果")
                                 .header(new ExcelHeader("ch1_light_t", "Tm_VIC", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch1_light_rm", "Rm_VIC", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch2_light_t", "Tm_Cy5", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch2_light_rm", "Rm_Cy5", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch3_light_t", "Tm_FAM", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch3_light_rm", "Rm_FAM", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch4_light_t", "Tm_ROX", CellDataType.NUMBER))
                                 .header(new ExcelHeader("ch4_light_rm", "Rm_ROX", CellDataType.NUMBER))
                                 .rows(dataStream);
        } else {
            return this.getTrim(recordId, exportAmplificationParam.getDeviceId(), type);
        }


    }


    /**
     * 查询es物模型的值
     *
     * @param recordId 批次id
     * @param deviceId 设备id
     * @param prop     物模型属性
     * @return Mono<PagerResult < DeviceProperty>>
     */
    private Mono<PagerResult<DeviceProperty>> getData(String recordId, String deviceId, String prop, boolean calc) {
        QueryParamEntity entity = new QueryParamEntity();
        Term termB = Term.of("batchId", "eq", recordId);
        Term termC = Term.of("calc", "eq", calc);
        Term termP = Term.of("property", "eq", prop);
        Sort sortT = new Sort("timestamp").asc();
        entity.addTerm(termB);
        if (calc) {
            entity.addTerm(termC);
        }
        entity.addTerm(termP);
        entity.setSorts(Collections.singletonList(sortT));
        entity.setPaging(false);
        return deviceDataService.queryPropertyPage(deviceId, entity, prop.split(","));
    }


    // 从数据库中获取文件路径
    public Flux<String> getExcelFilePaths() {
        // 模拟数据库查询返回文件路径
        return Flux.just(
            "D:\\data/1.xls",
            "D:\\data/2.xls"
        );
    }

    // 创建 ZIP 文件流
    public Mono<DataBuffer> createZipFromPaths(Flux<String> filePaths) {
        return Mono.create(sink -> {
            try {
                // 创建内存中的 ZIP 输出流
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                ZipOutputStream zipOutputStream = new ZipOutputStream(byteArrayOutputStream);

                filePaths
                    .flatMap(filePath -> {
                        try {
                            String storageBasePath = properties.getStorageBasePath();
                            Path path = Paths.get(storageBasePath, filePath);
                            if (!Files.exists(path)) {
                                return Mono.empty(); // 如果文件不存在，跳过
                            }

                            // 创建 ZIP 条目
                            ZipEntry zipEntry = new ZipEntry(path.getFileName().toString());
                            zipOutputStream.putNextEntry(zipEntry);

                            // 将文件写入 ZIP
                            try (InputStream inputStream = Files.newInputStream(path)) {
                                byte[] buffer = new byte[4096];
                                int bytesRead;
                                while ((bytesRead = inputStream.read(buffer)) != -1) {
                                    zipOutputStream.write(buffer, 0, bytesRead);
                                }
                            }
                            zipOutputStream.closeEntry();
                            return Mono.empty();
                        } catch (IOException e) {
                            return Mono.error(new RuntimeException("Error processing file: " + filePath, e));
                        }
                    })
                    .doOnComplete(() -> {
                        try {
                            // 完成压缩，关闭流
                            zipOutputStream.close();
                            sink.success(new DefaultDataBufferFactory().wrap(byteArrayOutputStream.toByteArray()));
                        } catch (IOException e) {
                            sink.error(e);
                        }
                    })
                    .subscribe();
            } catch (Exception e) {
                sink.error(e);
            }
        });
    }


    /**
     * 实验信息
     *
     * @param cTMetadada
     * @return
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getCTMetadada(CTMetadada cTMetadada) {

//      JSONObject  cTMetadada1= {"header":[10,100,1000,10000,100000,1000000],"ct":[["实验1",0,0,0,0,0,29.06],["实验2",0,0,0,0,28.91,30.27]],"statistics":[["平均值","0.00","0.00","0.00","0.00","14.46","29.66"],["标准差","0.00","0.00","0.00","0.00","14.45","0.60"]]}
        List<Integer> headers = cTMetadada.getHeader();
        List ct = cTMetadada.getCt();
        List sta = cTMetadada.getStatistics();

        String[] cell = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"};

        String cellStr = "C1:" + cell[headers.size() + 1] + "1";

        return sheet -> {


            sheet.name("CT值结果")
                 .cell(0, 2, "浓度")
                 //自定义sheet操作
                 .option(sheet_ -> {
                     sheet_.addMergedRegion(CellRangeAddress.valueOf(cellStr));
                 });
            sheet.cell(0, 0, "").option(sheet_ -> {
                sheet_.addMergedRegion(CellRangeAddress.valueOf("A1:B2"));
            });
            // headers
            for (int i = 0; i < headers.size(); i++) {
                sheet.cell(1, i + 2, headers.get(i));
            }
            Integer ctsize = ct.size() + 2;
            String ctStr = "A3:A" + ctsize;
            sheet.cell(2, 0, "ct").option(sheet_ -> {
                sheet_.addMergedRegion(CellRangeAddress.valueOf(ctStr));
            });
            // ct
            for (int i = 0; i < ct.size(); i++) {
                List<String> ct1 = (List<String>) ct.get(i);
                for (int j = 0; j < ct1.size(); j++) {
                    sheet.cell(i + 2, j + 1, ct1.get(j));
                }
            }

            Integer stasizes = ctsize + 1;
            Integer stasizee = ctsize + sta.size();
            String staStr = "A" + stasizes + ":A" + stasizee;
            sheet.cell(ctsize, 0, "统计").option(sheet_ -> {
                sheet_.addMergedRegion(CellRangeAddress.valueOf(staStr));
            });

            // 统计
            for (int i = 0; i < sta.size(); i++) {
                List<String> sta1 = (List<String>) sta.get(i);
                for (int j = 0; j < sta1.size(); j++) {
                    sheet.cell(i + ctsize, j + 1, sta1.get(j));
                }

            }
        };


    }


    public Consumer<SheetSpec.HeaderSheetSpec> getCTMetadada1(CTMetadada cTMetadada) {

        return sheet -> {

            sheet.name("分析结果")
                 .cell(0, 0, "斜率").cell(0, 1, cTMetadada.getSlope())
                 .cell(1, 0, "截距").cell(1, 1, cTMetadada.getIntercept())
                 .cell(2, 0, "R方").cell(2, 1, cTMetadada.getGoodness())
                 .cell(3, 0, "扩增效率").cell(3, 1, cTMetadada.getEfficiency());

        };

    }

    // 添加统计行辅助方法
    private void addStatisticsRow(FluxSink<Map<String, Object>> fluxSink,
                                  List<Integer> headers,
                                  List<String> stats,
                                  String rowName) {
        Map<String, Object> statRow = new LinkedHashMap<>();
        statRow.put("experiment_name", rowName);
        for (int i = 0; i < headers.size(); i++) {
            statRow.put("value_" + headers.get(i), stats.get(i + 1)); // 跳过行类型标识
        }
        fluxSink.next(statRow);
    }


    /**
     * 更新实验记录名称
     */
    public Mono<Integer> updateObjectName(ExperimentalRecordsInstanceEntity entity) {

        return this.createUpdate()
                   .set(ExperimentalRecordsInstanceEntity::getName, entity.getName())
                   .in(ExperimentalRecordsInstanceEntity::getId, entity.getId())
                   .execute();

    }

    public String updateValue(String v) {

        return v = v.equals("0") ? "" : v;

    }

    /**
     * 构建扩增数据分析sheet
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getAmplificationAnalysisSheet(ExportAmplificationParam param) {
        List<String> channels = Arrays.asList("FAM", "VIC", "ROX", "CY5");
        return sheet -> {
            sheet.name("扩增数据分析")
                 .header(new ExcelHeader("channel", "荧光通道", CellDataType.STRING))
                 .header(new ExcelHeader("baselineStart", "基线期起点", CellDataType.STRING))
                 .header(new ExcelHeader("baselineEnd", "基线期终点", CellDataType.STRING))
                 .header(new ExcelHeader("threshold", "阈值", CellDataType.STRING))
                 .header(new ExcelHeader("ctValue", "CT值", CellDataType.STRING));
            List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
            if ("扩增分析".equals(param.getChatTypeName()) && param.getAmplificationParams() != null) {
                for (ExportAmplificationParam.AmplificationParam amp : param.getAmplificationParams()) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    row.put("channel", amp.getName());
                    row.put("baselineStart", amp.getStart());
                    row.put("baselineEnd", amp.getEnd());
                    row.put("threshold", amp.getThreshold());
                    row.put("ctValue", amp.getCt());
                    rows.add(row);
                }
            } else {
                for (String channel : channels) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    row.put("channel", channel);
                    row.put("baselineStart", "");
                    row.put("baselineEnd", "");
                    row.put("threshold", "");
                    row.put("ctValue", "");
                    rows.add(row);
                }
            }
            sheet.rows(Flux.fromIterable(rows));
        };
    }

    /**
     * 构建熔解数据分析sheet
     */
    public Consumer<SheetSpec.HeaderSheetSpec> getMeltAnalysisSheet(ExportAmplificationParam param) {
        List<String> channels = Arrays.asList("FAM", "VIC", "ROX", "CY5");
        return sheet -> {
            sheet.name("熔解数据分析")
                 .header(new ExcelHeader("channel", "荧光通道", CellDataType.STRING))
                 .header(new ExcelHeader("thresholdType", "阈值类型", CellDataType.STRING))
                 .header(new ExcelHeader("threshold", "阈值", CellDataType.STRING));
            List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
            if ("熔解分析".equals(param.getChatTypeName()) && param.getMeltParams() != null) {
                for (ExportAmplificationParam.MeltParam melt : param.getMeltParams()) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    row.put("channel", melt.getName());
                    row.put("thresholdType", melt.getThresholdType());
                    row.put("threshold", melt.getThreshold());
                    rows.add(row);
                }
            } else {
                for (String channel : channels) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    row.put("channel", channel);
                    row.put("thresholdType", "");
                    row.put("threshold", "");
                    rows.add(row);
                }
            }
            sheet.rows(Flux.fromIterable(rows));
        };
    }

    public Mono<Integer> parsingExcel(FilePart filePart, String deviceId,String moduleId, String item, String projectName, String receiveTime, String id,String productId) {

        //将文件保存到本地，使用配置的存储基础路径
        String storageBasePath = properties.getStorageBasePath();
        String excelDir = storageBasePath + "/excel";
        String fileName = id + projectName + receiveTime + ".xlsx";
        String filePath = excelDir + "/" + fileName;

        // 确保目录存在
        File directory = new File(excelDir);
        if (!directory.exists()) {
            boolean created = directory.mkdirs();
            if (!created) {
                log.error("无法创建目录: {}", excelDir);
                return Mono.error(new RuntimeException("无法创建Excel文件存储目录: " + excelDir));
            }
            log.info("成功创建Excel文件存储目录: {}", excelDir);
        }

        log.info("=============================filePath: {}===============================", filePath);

        //打印除了文件的其他全部参数加上标记好检索
        log.info("=============================deviceId: {}===============================", deviceId);
        log.info("=============================moduleId: {}===============================", moduleId);
        log.info("=============================item: {}===============================", item);
        log.info("=============================projectName: {}===============================", projectName);
        log.info("=============================receiveTime: {}===============================", receiveTime);
        log.info("=============================id: {}===============================", id);
        log.info("=============================productId: {}===============================", productId);


        // 创建List保存所有解析结果
        List<Map<String, Object>> allResults = new ArrayList<>();
        String indexName = "properties_" + (StringUtils.isNotBlank(productId) ? productId.toLowerCase(Locale.ROOT) : "qpcrfast001");
        log.info("=============================indexName: {}===============================", indexName);

        // 先保存文件到本地，然后处理Excel内容
        return filePart.transferTo(new File(filePath))
                       .doOnSuccess(v -> log.info("文件保存成功: {}", filePath))
                       .doOnError(e -> log.error("文件保存失败: {}", filePath, e))
                       .then(filePart.content()
                       .collectList()
                       .flatMap(dataBuffers -> {
                           try {
                               // 将 DataBuffer 转换为字节数组
                               ByteArrayOutputStream baos = new ByteArrayOutputStream();
                               for (DataBuffer buffer : dataBuffers) {
                                   byte[] bytes = new byte[buffer.readableByteCount()];
                                   buffer.read(bytes);
                                   baos.write(bytes);
                                   DataBufferUtils.release(buffer);
                               }

                               byte[] excelBytes = baos.toByteArray();

                               // 使用 POI 读取 Excel 文件
                               try (Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(excelBytes))) {
                                   ExperimentalRecordsInstanceEntity instanceEntity = new ExperimentalRecordsInstanceEntity();
                                   // 解析每个 sheet
                                   parseRecordSheet(workbook.getSheet("实验信息"), instanceEntity);
                                   parseDeviceStateSheet(workbook.getSheet("状态信息"), instanceEntity);

                                   // 清理上次缓存
                                   temperatureMergedBuffer.get().clear();
                                   meltProcessedMergedBuffer.get().clear();
                                   parseTemperatureReportSheet(workbook.getSheet("温度"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseVolAndCurrentSheet(workbook.getSheet("电压电流"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseAmplificationSheet(workbook.getSheet("扩增"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseMeteringDataSheet(workbook.getSheet("荧光（电压）"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseMeteringSaveDataSheet(workbook.getSheet("荧光（原数据）"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseCTSheet(workbook.getSheet("CT"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, instanceEntity);
                                   parseMeltCurveSheet(workbook.getSheet("熔解（原数据）"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseMeltCurveCuleSheet(workbook.getSheet("熔解（后处理）"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);
                                   parseTrimSheet(workbook.getSheet("熔解结果"), instanceEntity.getDeviceId(), instanceEntity.getModuleId(), receiveTime, id, allResults);

                                   log.info("Excel 文件解析完成，共解析 {} 个 sheet", workbook.getNumberOfSheets());
                                   log.info("收集到的所有数据记录数量: {}", allResults.size());
                                   // 输出所有收集到的结果
                                   for (Map<String, Object> result : allResults) {
                                       log.info("解析结果: {}", JSON.toJSONString(result));
                                   }

                                   // 清理ThreadLocal缓存，避免内存泄漏
                                   temperatureMergedBuffer.remove();
                                   meltProcessedMergedBuffer.remove();

                                   QueryParam deleteParam = new QueryParam();
                                   Term term = Term.of("batchId", "eq", id);
                                   deleteParam.addTerm(term);


                                   // 设置从Excel解析出来的数据，如果没有解析到则使用默认值
//                                   instanceEntity.setItem(item);
//                                   instanceEntity.setDeviceId(deviceId);
//                                   instanceEntity.setModuleId(moduleId);
//                                   instanceEntity.setModuleCode(moduleId);
                                   instanceEntity.setReceiveTime(Long.parseLong(receiveTime));
                                   instanceEntity.setId(id);
                                   instanceEntity.setName(projectName);
                                   instanceEntity.setReagentNumber("000000");

                                   return super.deleteById(id).flatMap(r -> {
                                       return super.save(instanceEntity).flatMap(r2 ->{
                                           return elasticSearchService.delete(indexName, deleteParam)
                                                                      .flatMap(r3 -> {
                                                                          return elasticSearchService
                                                                              .commit(indexName, allResults)
                                                                              .doOnSuccess(entity -> log.info("成功保存数据: {}条到elasticsearch", allResults.size()))
                                                                              .thenReturn(workbook.getNumberOfSheets());
                                                                      });
                                       });
                                   });

                               }
                           } catch (Exception e) {
                               log.error("解析 Excel 文件失败", e);
                               return Mono.error(e);
                           }
                       }));
    }

    /**
     * 解析实验信息 sheet
     */
    private void parseRecordSheet(Sheet sheet, ExperimentalRecordsInstanceEntity instanceEntity) {
        if (sheet == null) return;

        try {
            log.info("开始解析实验信息 sheet: {}", sheet.getSheetName());

            // 解析标题信息（合并单元格）
            String title = getCellValueAsString(sheet.getRow(0).getCell(0));
            log.info("记录标题: {}", title);

            // 解析设备信息：设备名称（ID）_模块名称（ID）_实验人员账号
            String[] deviceInfo = parseDeviceInfo(title);
            if (deviceInfo.length >= 3) {
                // 设置设备名称和设备ID
                String deviceNameWithId = deviceInfo[0];
                String moduleCodeWithId = deviceInfo[1];
                String creatorWithUsername = deviceInfo[2];

                // 解析设备名称和ID
                String[] deviceNameParts = parseNameAndId(deviceNameWithId);
                String[] moduleParts = parseNameAndId(moduleCodeWithId);
                String[] creatorParts = parseNameAndId(creatorWithUsername);

                // 设置到instanceEntity
                instanceEntity.setDeviceName(deviceNameParts[0]);
                instanceEntity.setDeviceId(deviceNameParts[1]);
                instanceEntity.setModuleCode(moduleParts[0]);
                instanceEntity.setModuleId(moduleParts[1]);
                instanceEntity.setCreatorName(creatorParts[0]);
                instanceEntity.setCreatorUserName(creatorParts[1]);
            }

            parseCarrier(title,instanceEntity);
            // 解析检测项、测光通道、程序信息
            parseDetectionInfo(title, instanceEntity);

            // 解析完成度和实验时长
            parseProgressInfo(title, instanceEntity);

            // 解析表格数据并重建实验步骤
            List<Group> groups = new ArrayList<>();
            Map<String, Group> groupTypeMap = new HashMap<>();

            int firstRowIndex = 2; // 从第3行开始读取数据（第1行是标题，第2行是载体）
            for (int i = firstRowIndex; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                String type = getCellValueAsString(row.getCell(1)); // 类型列
                String temperature = getCellValueAsString(row.getCell(2)); // 温度列
                String speed = getCellValueAsString(row.getCell(3)); // 速度列
                String delay = getCellValueAsString(row.getCell(4)); // 时间列
                String light = getCellValueAsString(row.getCell(5)); // 是否测光列

                // 解析类型，确定是哪个组
                String groupType = parseGroupType(type);
                String loopCount = parseLoopCount(type);

                // 创建或获取组
                Group group = groupTypeMap.get(groupType);
                if (group == null) {
                    group = new Group();
                    group.setType(groupType);
                    group.setLoopCount(loopCount);
                    group.setTestLight(false); // 初始化为false，后续根据每行数据设置
                    group.setPanels(new ArrayList<>());
                    groupTypeMap.put(groupType, group);
                    groups.add(group);
                }

                // 创建面板
                Panel panel = new Panel();
                panel.setTemperature(temperature.replace("℃", ""));

                // 处理速度数据：转换为数值，去除单位
                try {
                    String cleanSpeed = speed.replaceAll("[^0-9.]", ""); // 只保留数字和小数点
                    if (!cleanSpeed.isEmpty()) {
                        panel.setSpeed(cleanSpeed);
                    } else {
                        panel.setSpeed("0"); // 默认值
                    }
                } catch (Exception e) {
                    log.warn("解析速度数据失败: {}, 使用默认值0", speed);
                    panel.setSpeed("0");
                }

                // 处理时间数据：转换为数值，去除单位
                try {
                    String cleanDelay = delay.replaceAll("[^0-9.]", ""); // 只保留数字和小数点
                    if (!cleanDelay.isEmpty()) {
                        panel.setDelay(cleanDelay);
                    } else {
                        panel.setDelay("0"); // 默认值
                    }
                } catch (Exception e) {
                    log.warn("解析时间数据失败: {}, 使用默认值0", delay);
                    panel.setDelay("0");
                }

                group.getPanels().add(panel);

                // 更新组的测光状态：如果任何一行是测光的，则整个组设置为测光
                if ("是".equals(light) || "Y".equals(light) || "1".equals(light)) {
                    group.setTestLight(true);
                }
            }

            // 构建SequenceStep并设置到instanceEntity
            if (!groups.isEmpty()) {
                SequenceStep sequenceStep = new SequenceStep();
                sequenceStep.setGroups(groups);
                String sequenceJson = JSON.toJSONString(sequenceStep);
                instanceEntity.setSequence(sequenceJson);
                log.info("重建的实验步骤序列: {}", sequenceJson);
            }

            log.debug("实验信息解析完成，instanceEntity: {}", JSON.toJSONString(instanceEntity));
        } catch (Exception e) {
            log.error("解析实验信息 sheet 失败", e);
        }
    }

    /**
     * 解析状态信息 sheet
     */
    private void parseDeviceStateSheet(Sheet sheet, ExperimentalRecordsInstanceEntity instanceEntity) {
        if (sheet == null) return;

        try {
            log.info("开始解析状态信息 sheet: {}", sheet.getSheetName());

            Map<String, String> deviceState = new HashMap<>();
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                String key = getCellValueAsString(row.getCell(0));
                String value = getCellValueAsString(row.getCell(1));
                if (key != null && !key.trim().isEmpty()) {
                    deviceState.put(key, value);
                }
            }

            log.info("状态信息数据: {}", deviceState);

            // 根据getDeviceStateData方法的中文逻辑，解析状态信息并设置到instanceEntity
            if (!deviceState.isEmpty()) {
                // 解析软件版本和模块版本
                if (deviceState.containsKey("设备端上位机版本号")) {
                    instanceEntity.setSoftwareVersion(deviceState.get("设备端上位机版本号"));
                }
                if (deviceState.containsKey("设备端下位机版本号")) {
                    instanceEntity.setModuleVersion(deviceState.get("设备端下位机版本号"));
                }

                // 构建moduleState JSON字符串，参考getDeviceStateData方法的逻辑
                Map<String, String> moduleStateMap = new HashMap<>();

                // 模块初始化状态
                if (deviceState.containsKey("模块初始化状态")) {
                    String initStatus = "是".equals(deviceState.get("模块初始化状态")) ? "1" : "0";
                    moduleStateMap.put("init", initStatus);
                }

                // 加热器初始化状态
                if (deviceState.containsKey("加热器初始化状态")) {
                    String heaterInitStatus = "是".equals(deviceState.get("加热器初始化状态")) ? "1" : "0";
                    moduleStateMap.put("heater_init_stauts", heaterInitStatus);
                }

                // 加热器校准状态
                if (deviceState.containsKey("加热器校准状态")) {
                    String calibrationStatus = "是".equals(deviceState.get("加热器校准状态")) ? "1" : "0";
                    moduleStateMap.put("calibration", calibrationStatus);
                }

                // 加热器参数
                if (deviceState.containsKey("加热器参数")) {
                    String heaterParams = deviceState.get("加热器参数");
                    // 解析R1、R2、H2参数
                    parseHeaterParameters(heaterParams, moduleStateMap);
                }

                // 加热器健康状态
                if (deviceState.containsKey("加热器健康状态")) {
                    String heaterStatus = deviceState.get("加热器健康状态");
                    if (heaterStatus.startsWith("正常")) {
                        moduleStateMap.put("heater_err_str", "");
                    } else if (heaterStatus.startsWith("异常:")) {
                        String errorMsg = heaterStatus.substring(3);
                        moduleStateMap.put("heater_err_str", errorMsg);
                    }
                    moduleStateMap.put("heater_status", "1"); // 默认启用
                }

                // 冷却器初始化状态
                if (deviceState.containsKey("冷却器初始化状态")) {
                    String coolerInitStatus = "是".equals(deviceState.get("冷却器初始化状态")) ? "1" : "0";
                    moduleStateMap.put("water_cooler_init_status", coolerInitStatus);
                }

                // 冷却器健康状态
                if (deviceState.containsKey("冷却器健康状态")) {
                    String coolerStatus = deviceState.get("冷却器健康状态");
                    if (coolerStatus.startsWith("正常")) {
                        moduleStateMap.put("water_cooler_errer", "0");
                        moduleStateMap.put("water_cooler_err_str", "");
                    } else if (coolerStatus.startsWith("异常：")) {
                        String errorMsg = coolerStatus.substring(3);
                        moduleStateMap.put("water_cooler_errer", "1");
                        moduleStateMap.put("water_cooler_err_str", errorMsg);
                    }
                    moduleStateMap.put("water_cooler_seft_check", "3"); // 默认值
                }

                // 测光通道状态
                for (int i = 1; i <= 4; i++) {
                    String chPrefix = "ch" + i;

                    // 初始化状态
                    String initKey = "测光通道" + i + "初始化状态";
                    if (deviceState.containsKey(initKey)) {
                        String initStatus = "是".equals(deviceState.get(initKey)) ? "1" : "0";
                        moduleStateMap.put(chPrefix + "_init_status", initStatus);
                    }

                    // 健康状态
                    String healthKey = "测光通道" + i + "健康状态";
                    if (deviceState.containsKey(healthKey)) {
                        String healthStatus = deviceState.get(healthKey);
                        if (healthStatus.startsWith("正常")) {
                            moduleStateMap.put(chPrefix + "_run_status", "0");
                            moduleStateMap.put(chPrefix + "_err_str", "");
                        } else if (healthStatus.startsWith("异常:")) {
                            String errorMsg = healthStatus.substring(3);
                            moduleStateMap.put(chPrefix + "_run_status", "1");
                            moduleStateMap.put(chPrefix + "_err_str", errorMsg);
                        }
                    }
                }

                // 设置默认值
                moduleStateMap.put("batchId", "");
                moduleStateMap.put("carrier", "1");
                moduleStateMap.put("cover_plate", "1");
                moduleStateMap.put("cover_plate_carrier_status", "0");
                moduleStateMap.put("film_carrier", "0");
                moduleStateMap.put("injection_carrier", "1");
                moduleStateMap.put("indenter_status", "1");
                moduleStateMap.put("leave_time", "0");
                moduleStateMap.put("schedule", "100");
                moduleStateMap.put("status", "1");
                moduleStateMap.put("temperature_error", "0");
                moduleStateMap.put("temperature_uniform", "0");
                moduleStateMap.put("temperature_wave", "0");
                moduleStateMap.put("valve_vol", "0");
                moduleStateMap.put("water_cooler_box_temp", "0");
                moduleStateMap.put("cold_head_temp", "0");
                moduleStateMap.put("heater_reference_temp", "3000");
                moduleStateMap.put("heater_rtd_temp", "3000");

                // 将解析的状态信息转换为JSON字符串并设置到instanceEntity
                String moduleStateJson = JSON.toJSONString(moduleStateMap);
                instanceEntity.setModuleState(moduleStateJson);
                log.info("解析的设备状态信息: {}", moduleStateJson);
            }
        } catch (Exception e) {
            log.error("解析状态信息 sheet 失败", e);
        }
    }

    /**
     * 解析温度 sheet
     */
    private void parseTemperatureReportSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析温度 sheet: {}", sheet.getSheetName());

            // 固定第一行为表头
            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            log.debug("温度 sheet 原始表头: {}", columnMap);
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);
            log.debug("温度 sheet 表头映射: {}", indexToField);

            // 解析数据行，以 sample_time 为 key 缓存温度数据
            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                // 以 sample_time 为 key 缓存温度数据，将秒转换为毫秒
                Object sampleTime = rowData.get("sample_time");
                if (sampleTime != null) {
                    try {
                        // 将秒转换为毫秒
                        double seconds = Double.parseDouble(String.valueOf(sampleTime));
                        long milliseconds = (long) (seconds * 1000);
                        String timeKey = String.valueOf(milliseconds);

                        // 更新 rowData 中的 sample_time 为毫秒值
                        rowData.put("sample_time", milliseconds);

                        temperatureMergedBuffer.get().put(timeKey, rowData);
                        log.debug("缓存温度数据，时间: {}ms (原值: {}s), 数据: {}", milliseconds, seconds, rowData);
                    } catch (NumberFormatException e) {
                        log.warn("温度数据 sample_time 字段格式错误，行: {}, 值: {}", i, sampleTime);
                    }
                } else {
                    log.warn("温度数据缺少 sample_time 字段，行: {}, 可用字段: {}", i, rowData.keySet());
                }
            }
        } catch (Exception e) {
            log.error("解析温度 sheet 失败", e);
        }
    }

    /**
     * 解析电压电流 sheet
     */
    private void parseVolAndCurrentSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析电压电流 sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            log.debug("电压电流 sheet 原始表头: {}", columnMap);
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);
            log.debug("电压电流 sheet 表头映射: {}", indexToField);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                // 以 sample_time 为 key 与温度数据合并，将秒转换为毫秒
                Object sampleTime = rowData.get("sample_time");
                if (sampleTime != null) {
                    try {
                        // 将秒转换为毫秒
                        double seconds = Double.parseDouble(String.valueOf(sampleTime));
                        long milliseconds = (long) (seconds * 1000);
                        String timeKey = String.valueOf(milliseconds);

                        // 更新 rowData 中的 sample_time 为毫秒值
                        rowData.put("sample_time", milliseconds);

                        Map<String, Object> merged = new HashMap<>();

                        // 获取对应时间的温度数据
                        Map<String, Object> tempData = temperatureMergedBuffer.get().get(timeKey);
                        if (tempData != null) {
                            merged.putAll(tempData);
                            log.debug("找到对应时间的温度数据，时间: {}ms (原值: {}s), 温度数据: {}", milliseconds, seconds, tempData);
                        } else {
                            log.warn("未找到对应时间的温度数据，时间: {}ms (原值: {}s)", milliseconds, seconds);
                        }

                        // 添加电压电流数据
                        merged.putAll(rowData);

                        // 构建合并后的结果对象并添加到结果列表
                        Map<String, Object> result = buildMergedRecord("温度+电压电流", "TemperatureReportData", merged, deviceId, moduleId, receiveTime, id);
                        allResults.add(result);

                        log.debug("合并后的数据，时间: {}ms (原值: {}s), 数据: {}", milliseconds, seconds, merged);
                    } catch (NumberFormatException e) {
                        log.warn("电压电流数据 sample_time 字段格式错误，行: {}, 值: {}", i, sampleTime);
                    }
                } else {
                    log.warn("电压电流数据缺少 sample_time 字段，行: {}", i);
                }
            }
        } catch (Exception e) {
            log.error("解析电压电流 sheet 失败", e);
        }
    }

    /**
     * 解析扩增 sheet
     */
    private void parseAmplificationSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析扩增 sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析扩增 sheet 失败", e);
        }
    }

    /**
     * 解析荧光（电压） sheet
     */
    private void parseMeteringDataSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析荧光（电压） sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析荧光（电压） sheet 失败", e);
        }
    }

    /**
     * 解析荧光(源数据) sheet
     */
    private void parseMeteringSaveDataSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析荧光(源数据) sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析荧光(源数据) sheet 失败", e);
        }
    }

    /**
     * 解析 CT sheet
     */
    private void parseCTSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id,ExperimentalRecordsInstanceEntity entity) {
        if (sheet == null) return;

        try {
            log.info("开始解析 CT sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            // CT 页只有一行数据：读取第一条数据行并直接写入 entity
            int dataRowIndex = headerRowIndex + 1;
            if (dataRowIndex <= sheet.getLastRowNum()) {
                Row row = sheet.getRow(dataRowIndex);
                if (row != null) {
                                    Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                    try {
                        Object vic = rowData.get("vic");
                        if (vic != null && !String.valueOf(vic).isEmpty()) {
                            entity.setVic(Double.valueOf(String.valueOf(vic)));
                        }
                        Object cy5 = rowData.get("cy5");
                        if (cy5 != null && !String.valueOf(cy5).isEmpty()) {
                            entity.setCy5(Double.valueOf(String.valueOf(cy5)));
                        }
                        Object fam = rowData.get("fam");
                        if (fam != null && !String.valueOf(fam).isEmpty()) {
                            entity.setFam(Double.valueOf(String.valueOf(fam)));
                        }
                        Object rox = rowData.get("rox");
                        if (rox != null && !String.valueOf(rox).isEmpty()) {
                            entity.setRox(Double.valueOf(String.valueOf(rox)));
                        }
                    } catch (Exception ignore) {
                        // 忽略单个字段转换异常，避免影响整体解析
                    }
                }
            }
        } catch (Exception e) {
            log.error("解析 CT sheet 失败", e);
        }
    }

    /**
     * 解析熔解（原数据） sheet
     */
    private void parseMeltCurveSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析熔解（原数据） sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析熔解（原数据） sheet 失败", e);
        }
    }

    /**
     * 解析熔解（后处理） sheet
     */
    private void parseMeltCurveCuleSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析熔解（后处理） sheet: {}", sheet.getSheetName());

            int headerRowIndex = 0;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            // 解析数据行，以行号为 key 缓存熔解（后处理）数据
            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                // 以行号为 key 缓存熔解（后处理）数据
                String rowKey = String.valueOf(i);
                meltProcessedMergedBuffer.get().put(rowKey, rowData);
                log.debug("缓存熔解（后处理）数据，行号: {}, 数据: {}", i, rowData);
            }
        } catch (Exception e) {
            log.error("解析熔解（后处理） sheet 失败", e);
        }
    }

    /**
     * 解析熔解结果 sheet
     */
    private void parseTrimSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析熔解结果 sheet: {}", sheet.getSheetName());

            int headerRowIndex = findHeaderRow(sheet);
            if (headerRowIndex == -1) return;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }
            Map<Integer, String> indexToField = mapHeadersToFields(sheet.getSheetName(), columnMap);

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : indexToField.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                // 以行号为 key 与熔解（后处理）数据合并
                String rowKey = String.valueOf(i);
                Map<String, Object> merged = new HashMap<>();

                // 获取对应行号的熔解（后处理）数据
                Map<String, Object> processedData = meltProcessedMergedBuffer.get().get(rowKey);
                if (processedData != null) {
                    merged.putAll(processedData);
                    log.debug("找到对应行号的熔解（后处理）数据，行号: {}, 后处理数据: {}", i, processedData);
                } else {
                    log.warn("未找到对应行号的熔解（后处理）数据，行号: {}", i);
                }

                // 添加熔解结果数据
                merged.putAll(rowData);

                // 构建合并后的结果对象并添加到结果列表
                Map<String, Object> result = buildMergedRecord("熔解（后处理）+熔解结果", "MeltCurveCalcData", merged, deviceId, moduleId, receiveTime, id);
                allResults.add(result);

                log.debug("合并后的熔解数据，行号: {}, 数据: {}", i, merged);
            }
        } catch (Exception e) {
            log.error("解析熔解结果 sheet 失败", e);
        }
    }

    /**
     * 解析扩增数据分析 sheet
     */
    private void parseAmplificationAnalysisSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析扩增数据分析 sheet: {}", sheet.getSheetName());

            int headerRowIndex = findHeaderRow(sheet);
            if (headerRowIndex == -1) return;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : columnMap.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析扩增数据分析 sheet 失败", e);
        }
    }

    /**
     * 解析熔解数据分析 sheet
     */
    private void parseMeltAnalysisSheet(Sheet sheet, String deviceId, String moduleId, String receiveTime, String id, List<Map<String, Object>> allResults) {
        if (sheet == null) return;

        try {
            log.info("开始解析熔解数据分析 sheet: {}", sheet.getSheetName());

            int headerRowIndex = findHeaderRow(sheet);
            if (headerRowIndex == -1) return;

            Row headerRow = sheet.getRow(headerRowIndex);
            Map<Integer, String> columnMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnMap.put(i, getCellValueAsString(cell));
                }
            }

            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Map<String, Object> rowData = new HashMap<>();
                for (Map.Entry<Integer, String> entry : columnMap.entrySet()) {
                    String cellValue = getCellValueAsString(row.getCell(entry.getKey()));
                    if (cellValue != null) {
                        rowData.put(entry.getValue(), cellValue);
                    }
                }

                Map<String, Object> result = buildRecord(sheet.getSheetName(), rowData, deviceId, moduleId, receiveTime, id);
                if (result != null) {
                    allResults.add(result);
                }
            }
        } catch (Exception e) {
            log.error("解析熔解数据分析 sheet 失败", e);
        }
    }

    /**
     * 查找表头行
     */
    private int findHeaderRow(Sheet sheet) {
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            // 检查是否包含常见的表头关键词
            boolean hasHeader = false;
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    String value = getCellValueAsString(cell);
                    if (value != null && (value.contains("Cycle") || value.contains("温度") ||
                        value.contains("电压") || value.contains("电流") || value.contains("通道") ||
                        value.contains("VIC") || value.contains("FAM") || value.contains("ROX") ||
                        value.contains("CY5") || value.contains("CT") || value.contains("熔解"))) {
                        hasHeader = true;
                        break;
                    }
                }
            }

            if (hasHeader) {
                return i;
            }
        }
        return -1;
    }

    /**
     * 解析设备信息
     */
    private String[] parseDeviceInfo(String title) {
        String[] lines = title.split("\n");
        if (lines.length > 0) {
            return lines[0].split("_");
        }
        return new String[0];
    }

    /**
     * 解析名称和ID
     */
    private String[] parseNameAndId(String nameWithId) {
        if (nameWithId.contains("(") && nameWithId.contains(")")) {
            String name = nameWithId.substring(0, nameWithId.indexOf("("));
            String id = nameWithId.substring(nameWithId.indexOf("(") + 1, nameWithId.indexOf(")"));
            return new String[]{name, id};
        }
        return new String[]{nameWithId, ""};
    }

    /**
     * 解析检测项、测光通道、程序信息
     */
    private void parseDetectionInfo(String title, ExperimentalRecordsInstanceEntity instanceEntity) {
        String[] lines = title.split("\n");

        log.info("开始解析检测项、测光通道、程序信息，标题行数: {}", lines.length);
        for (int i = 0; i < lines.length; i++) {
            String line = lines[i];
            log.info("第{}行内容: [{}]", i, line);

            if (line.contains("检测项:")) {
                log.info("找到检测项行: {}", line);
                parseDetectionItem(line, instanceEntity);
            }
            if (line.contains("测光通道:")) {
                log.info("找到测光通道行: {}", line);
                parseLightChannel(line, instanceEntity);
            }
            if (line.contains("程序:")) {
                log.info("找到程序行: {}", line);
                parseSequence(line, instanceEntity);
            }
        }

        log.info("解析完成后的instanceEntity状态:");
        log.info("检测项: {}, 状态: {}", instanceEntity.getItem(), instanceEntity.getItemUpStatus());
        log.info("测光通道: {}, 状态: {}", instanceEntity.getLightChannel(), instanceEntity.getLightUpStatus());
        log.info("程序名称: {}, 状态: {}", instanceEntity.getSeqName(), instanceEntity.getSeqUpStatus());
    }


    private void parseCarrier(String title, ExperimentalRecordsInstanceEntity instanceEntity) {
        String[] lines = title.split("\n");
        if (lines.length >= 2) {
            instanceEntity.setCarrier(lines[1]);
        }
    }


    /**
     * 解析检测项信息
     */
    private void parseDetectionItem(String line, ExperimentalRecordsInstanceEntity instanceEntity) {
        log.info("开始解析检测项信息: {}", line);
        // 检测项:（库+检测项名）或者（用户自定义+检测项名）
        String[] split = line.split("\\),");
        if (split.length > 0){
            instanceEntity.setItemUpStatus(0); // 库
            String itemStr = split[0];
            if (StringUtils.isNotBlank(itemStr)){
                if (itemStr.contains("检测项:（库")){
                    String itemName = split[0].replace("检测项:（库","");
                    instanceEntity.setItem(itemName);
                }else {
                    String itemName = split[0].replace("检测项:（","");
                    instanceEntity.setItem(itemName);
                }
            }
        }else {
            log.warn("未识别的检测项格式: {}", line);
        }
    }

    /**
     * 解析测光通道信息
     */
    private void parseLightChannel(String line, ExperimentalRecordsInstanceEntity instanceEntity) {
        log.info("开始解析测光通道信息: {}", line);
        // 测光通道:（库 + 测光通道）或者（用户自定义 + 测光通道）
        String[] split = line.split("\\),");
        if (split.length > 0){
            instanceEntity.setLightUpStatus(0); // 库
            String channelStr = split[1];
            if (StringUtils.isNotBlank(channelStr)){
                if (channelStr.contains("测光通道:（库")){
                    String channelName = split[1].replace("测光通道:（库","");
                    instanceEntity.setLightChannel(channelName);
                }else {
                    String channelName = split[1].replace("测光通道:（","");
                    instanceEntity.setLightChannel(channelName);
                }
            }
        } else {
            log.warn("未识别的测光通道格式: {}", line);
        }
    }

    /**
     * 解析程序信息
     */
    private void parseSequence(String line, ExperimentalRecordsInstanceEntity instanceEntity) {
        log.info("开始解析程序信息: {}", line);
        // 程序:（库 + 程序名称）或者（用户自定义 + 程序名称）
        String[] split = line.split("\\),");
        if (split.length > 0){
            instanceEntity.setSeqUpStatus(0); // 库
            String seqStr = split[2];
            if (StringUtils.isNotBlank(seqStr)){
                if (seqStr.contains("程序:（库")){
                    String seqName = split[2].replace("程序:（库","");
                    instanceEntity.setSeqName(seqName);
                    log.info("解析到库程序: {}, 状态: 0", seqName);
                }else {
                    String seqName = split[2].replace("程序:（","");
                    instanceEntity.setSeqName(seqName);
                    log.info("解析到用户自定义程序: {}, 状态: 0", seqName);
                }
            }
        }else {
            log.warn("未识别的程序格式: {}", line);
        }
    }

    /**
     * 解析完成度和实验时长
     */
    private void parseProgressInfo(String title, ExperimentalRecordsInstanceEntity instanceEntity) {
        String[] lines = title.split("\n");

        for (String line : lines) {
            if (line.contains("完成度:")) {
                // 解析完成度百分比
                int progressIndex = line.indexOf("完成度:") + 4;
                int percentIndex = line.indexOf("%", progressIndex);
                if (percentIndex > progressIndex) {
                    String progressStr = line.substring(progressIndex, percentIndex);
                    try {
                        int progress = Integer.parseInt(progressStr);
                        instanceEntity.setState(String.valueOf(progress));
                    } catch (NumberFormatException e) {
                        log.warn("解析完成度失败: {}", progressStr);
                    }
                }

                // 解析实验时长
                if (line.contains("实验时长：")) {
                    parseRunTime(line, instanceEntity);
                }
            }
        }
    }

    /**
     * 解析实验时长
     */
    private void parseRunTime(String line, ExperimentalRecordsInstanceEntity instanceEntity) {
        // 实验时长：x分x秒
        int timeIndex = line.indexOf("实验时长：") + 5;
        String timeStr = line.substring(timeIndex);

        try {
            String[] timeParts = timeStr.split("分");
            if (timeParts.length == 2) {
                int minutes = Integer.parseInt(timeParts[0]);
                int seconds = Integer.parseInt(timeParts[1].replace("秒", ""));

                // 转换为毫秒
                int totalMillis = (minutes * 60 + seconds) * 1000;
                instanceEntity.setRunTime(String.valueOf(totalMillis));
            }
        } catch (NumberFormatException e) {
            log.warn("解析实验时长失败: {}", timeStr);
        }
    }

    /**
     * 解析组类型
     */
    private String parseGroupType(String type) {
        if (type.contains("恒温段")) {
            return "1";
        } else if (type.contains("变温段")) {
            return "2";
        } else if (type.contains("熔解段")) {
            return "3";
        }
        return "1"; // 默认
    }

    /**
     * 解析循环次数
     */
    private String parseLoopCount(String type) {
        if (type.contains("(") && type.contains(")")) {
            String loopStr = type.substring(type.indexOf("(") + 1, type.indexOf(")"));
            try {
                Integer.parseInt(loopStr);
                return loopStr;
            } catch (NumberFormatException e) {
                // 忽略
            }
        }
        return "1"; // 默认循环1次
    }

    /**
     * 解析加热器参数
     */
    private void parseHeaterParameters(String heaterParams, Map<String, String> moduleStateMap) {
        // 加热器参数格式：R1(12373608),R2(4566479),H2(2712828)
        try {
            if (heaterParams.contains("R1(") && heaterParams.contains("R2(") && heaterParams.contains("H2(")) {
                // 提取R1值
                int r1Start = heaterParams.indexOf("R1(") + 3;
                int r1End = heaterParams.indexOf(")", r1Start);
                if (r1Start < r1End) {
                    String r1Value = heaterParams.substring(r1Start, r1End);
                    moduleStateMap.put("R1", r1Value);
                }

                // 提取R2值
                int r2Start = heaterParams.indexOf("R2(") + 3;
                int r2End = heaterParams.indexOf(")", r2Start);
                if (r2Start < r2End) {
                    String r2Value = heaterParams.substring(r2Start, r2End);
                    moduleStateMap.put("R2", r2Value);
                }

                // 提取H2值
                int h2Start = heaterParams.indexOf("H2(") + 3;
                int h2End = heaterParams.indexOf(")", h2Start);
                if (h2Start < h2End) {
                    String h2Value = heaterParams.substring(h2Start, h2End);
                    moduleStateMap.put("H2", h2Value);
                }
            }
        } catch (Exception e) {
            log.warn("解析加热器参数失败: {}", heaterParams, e);
        }
    }

    /**
     * 获取单元格值作为字符串
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return null;

        switch (cell.getCellType()) {
            case STRING:
                String strValue = cell.getStringCellValue();
                return strValue == null || strValue.trim().isEmpty() ? null : strValue;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    return cell.getStringCellValue();
                }
            case BLANK:
                return null;
            default:
                return null;
        }
    }

    // 根据 sheet 名称与第一行表头，构建 列索引 -> 存储字段 的映射
    private Map<Integer, String> mapHeadersToFields(String sheetName, Map<Integer, String> columnIndexToHeader) {
        Map<Integer, String> result = new HashMap<>();
        Map<String, List<MappingRule>> allMappings = SheetMappings.all();
        List<MappingRule> rules = allMappings.get(sheetName);
        if (rules == null || rules.isEmpty()) {
            return result;
        }
        // 仅精确匹配你定义的标题（不做任何扩展/模糊匹配）
        for (Map.Entry<Integer, String> entry : columnIndexToHeader.entrySet()) {
            Integer colIdx = entry.getKey();
            String header = entry.getValue();
            if (header == null) continue;
            for (MappingRule rule : rules) {
                for (String h : rule.getHeaders()) {
                    if (header.equals(h)) {
                        result.put(colIdx, rule.getField());
                        break;
                    }
                }
            }
        }
        return result;
    }

    // 构建合并记录（温度+电压电流）
    private Map<String, Object> buildMergedRecord(String sheetName, String bizId, Map<String, Object> data, String deviceId, String moduleId, String receiveTime, String id) {
        Map<String, Object> out = new HashMap<>();
        out.put("id", String.valueOf(System.nanoTime()));
        out.put("deviceId", deviceId);
        out.put("timestamp", System.currentTimeMillis());
        out.put("property", bizId);
        out.put("createTime", receiveTime == null ? null : Long.parseLong(receiveTime));
        out.put("batchId", id == null ? "" : id);
        out.put("moduleId", moduleId == null ? "" : moduleId);
        out.put("bizId", bizId);
        out.put("unitId", "");
        out.put("userId", "");
        out.put("calc", false);
        out.put("type", "string");
        out.put("value", JSON.toJSONString(data));
        return out;
    }

    // 构建普通记录
    private Map<String, Object> buildRecord(String sheetName, Map<String, Object> data, String deviceId, String moduleId, String receiveTime, String id) {
        String bizId = SheetMappings.sheetToBizId().get(sheetName);
        if (bizId == null) {
            return null; // 未配置 bizId 的 sheet 跳过
        }
        Map<String, Object> out = new HashMap<>();
        out.put("id", String.valueOf(System.nanoTime()));
        out.put("deviceId", deviceId);
        out.put("timestamp", System.currentTimeMillis());
        out.put("property", bizId);
        out.put("createTime", receiveTime == null ? null : Long.parseLong(receiveTime));
        out.put("batchId", id == null ? "" : id);
        out.put("moduleId", moduleId == null ? "" : moduleId);
        out.put("bizId", bizId);
        out.put("unitId", "");
        out.put("userId", "");
        out.put("calc", false);
        out.put("type", "string");
        out.put("value", JSON.toJSONString(data));
        return out;
    }
}




